package revolucion.database;

import revolucion.classes.Log;

import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;

import java.util.Vector;

import javax.swing.JOptionPane;
import javax.swing.table.DefaultTableModel;

public class Database {
  static Connection conn;
  static String status = "";
  static Statement stmt;
  static ResultSet rs;
  static ResultSetMetaData rsmt;

  public static Connection getConnection()
  {
    try {
      Class.forName("com.mysql.jdbc.Driver").newInstance();

      String url = "jdbc:mysql://201.20.24.62:3306/projetoa_portal?user=projetoa_admin&password=P@55w0rd";
      conn = DriverManager.getConnection(url);

      status = "Conexao aberta !!";
    }
    catch(SQLException e)
    {
      status = e.getMessage();
    }
    catch(ClassNotFoundException e)
    {
      status = e.getMessage();
    }
    catch(Exception e)
    {
      status = e.getMessage();
    }

    return conn;
  }


  //===== USERS OPERATIONS =====//
  public static ResultSet getUsers() {
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT * FROM users");
    } catch(Exception e) {
      e.printStackTrace();
    }

    return rs;
  }

  public static int countUsers() {
    int result = 0;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT COUNT(*) FROM users");
      rs.next();
      result = rs.getInt(1);
      rs.close();
    } catch ( Exception e) {
      e.printStackTrace();
    }

    return result;
  }

  public static ResultSet getUsersData() {
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT user, displayName, email FROM users");
    } catch ( SQLException e ) {
      e.printStackTrace();
    }

    return rs;
  }

  public static void saveUser( String _user, String _field, String _data ) {
    try {
      stmt = conn.createStatement();
      stmt.execute("UPDATE users SET "+_field+"='"+_data+"' WHERE user='"+_user+"'");
    } catch ( Exception e ) {
      e.printStackTrace();
    }
  }

  public static boolean deleteUser(String _user) {
    boolean result;

    try {
      stmt = conn.createStatement();
      stmt.execute("DELETE FROM users WHERE user='"+_user+"'");
      
      result = true;
    } catch ( Exception e ) {
      e.printStackTrace();
      result = false;
    }

    return result;
  }

  public static boolean checkUser( String _user, String _pass ) {
    boolean exist = false;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT COUNT(*) FROM users WHERE user='"+_user+"' AND pass='"+_pass+"'");
      while(rs.next()) {
        if(rs.getInt(1) != 0)
          exist = true;
        else
          exist = false;
      }
      
      rs.close();
    } catch ( Exception e) {
      e.printStackTrace();
    }

    return exist;
  }

  public static DefaultTableModel getUsersTableModel() {
    DefaultTableModel md = new DefaultTableModel();
    
    try {
      ResultSet users = Database.getUsersData();
      rsmt = users.getMetaData();
      Vector vectColumn = new Vector();
      vectColumn.add("Usuário");
      vectColumn.add("Nome");
      vectColumn.add("Email");

      Vector vectLines = new Vector();

      while(users.next()){
        Vector vectLine = new Vector();

        for(int i = 0;i < rsmt.getColumnCount();i++)
          vectLine.add(users.getObject(i+1));

        vectLines.add(vectLine);
      }

      DefaultTableModel newModel = new DefaultTableModel(vectLines, vectColumn) {
        public boolean isCellEditable(int rowIndex, int mColIndex) {
          return false;
        }
      };

      md = newModel;
    } catch(Exception e) {
      
    }

    return md;
  }
  //===== END USERS OPERATIONS =====//



  //===== REGISTERED OPERATIONS =====//
  public static ResultSet getRegisteredsData () {
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT * FROM registereds");
    } catch ( Exception e) {
      e.printStackTrace();
    }

    return rs;
  }

  public static DefaultTableModel getRegisteredsTableModel () {
    DefaultTableModel md = new DefaultTableModel();

    try {
      ResultSet users = Database.getRegisteredsData();
      rsmt = users.getMetaData();
      Vector vectColumn = new Vector();
      
      vectColumn.add("Nome");
      vectColumn.add("Idade");
      vectColumn.add("Endereço");
      vectColumn.add("Email");
      vectColumn.add("Telefone");

      Vector vectLines = new Vector();

      while(users.next()){
        Vector vectLine = new Vector();

        for(int i = 0;i < rsmt.getColumnCount();i++)
          vectLine.add(users.getObject(i+1));

        vectLines.add(vectLine);
      }

      DefaultTableModel newModel = new DefaultTableModel(vectLines, vectColumn) {
        public boolean isCellEditable(int rowIndex, int mColIndex) {
          return false;
        }
      };

      md = newModel;
    } catch(Exception e) {

    }

    return md;
  }
  //===== END REGISTERED OPERATIONS =====//



  //===== PASSWORDS OPERATIONS =====//
  public static boolean checkPass(String _session, String _pass) {
    boolean valid = false;

    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT COUNT(*) FROM passwords WHERE session='"+_session+"' AND pass='"+_pass+"'");
      while(rs.next()) {
        if(rs.getInt(1) != 0)
          valid = true;
        else
          valid = false;
      }
    } catch ( SQLException e ) {
      e.printStackTrace();
    }

    return valid;
  }
  //===== END PASSWORDS OPERATIONS =====//



  //===== LOGS OPERATIONS =====//

  public static ResultSet getLogsData() {
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT user, logText, stackTrace FROM logs");
    } catch ( SQLException e ) {
      e.printStackTrace();
    }

    return rs;
  }
  
  public static DefaultTableModel getLogsTableModel() {
    DefaultTableModel md = new DefaultTableModel();

    try {
      ResultSet logs = Database.getLogsData();
      rsmt = logs.getMetaData();
      Vector vectColumn = new Vector();
      vectColumn.add("Usuário");
      vectColumn.add("Log");
      vectColumn.add("Detalhes");

      Vector vectLines = new Vector();

      while(logs.next()){
        Vector vectLine = new Vector();

        for(int i = 0;i < rsmt.getColumnCount();i++)
          vectLine.add(logs.getObject(i+1));

        vectLines.add(vectLine);
      }

      DefaultTableModel newModel = new DefaultTableModel(vectLines, vectColumn) {
        public boolean isCellEditable(int rowIndex, int mColIndex) {
          return false;
        }
      };

      md = newModel;
    } catch(Exception e) {

    }

    return md;
  }
  //===== END LOGS OPERATIONS =====//



  //===== CHAT OPERATIONS =====//
  public static ResultSet getChatMessages() {
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT owner, message, data FROM chat ORDER BY data ASC");
    } catch ( SQLException e ) {
      e.printStackTrace();
    }

    return rs;
  }

  public static ResultSet getUsersOnline() {
    try {
      stmt = conn.createStatement();
      rs = stmt.executeQuery("SELECT displayName, status FROM users WHERE status='online' ORDER BY user");
    } catch ( SQLException e ) {
      e.printStackTrace();
    }

    return rs;
  }

  public static void sendChatMessage( String _owner, String _message ) {
    conn = Database.getConnection();

    try {
      stmt.execute("INSERT INTO chat VALUES(null,'"+_owner+"', '"+_message+"', NOW())");
    } catch ( SQLException e) {
      JOptionPane.showMessageDialog(null, "Ocorreu um erro ao enviar sua mensagem!\nContate o administrador do sistema.");
      Log.writeLog( _owner, "Erro ao enviar mensagem do chat.", e.getMessage() );
      System.out.println(e.getMessage());
    }
  }
  //===== END CHAT OPERATIONS =====//
}
